package jdbcmapper;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.*;

import lotus.domino.EmbeddedObject;
import lotus.domino.NotesException;

import com.robaone.dbase.types.DateType;
/**
 * <p>Title: CCMS/Notes Domino Agents</p>
 * <p>Description: Agents that syncronize data between Notes and MySQL server</p>
 * <p>Copyright: Copyright (c) 2005</p>
 * <p>Company: The Salvation Army</p>
 * @author Ansel Robateau
 * @version 1.0
 */

abstract public class BaseDO {
  protected Hashtable<String, SQLField> m_fields;
  protected String m_tablename;
  protected Connection m_con;
  protected Vector<FieldMap> m_primarykeys;
  protected Vector<Object> m_log;
  protected Vector<String> m_keys = null;
  public BaseDO(String tablename,Connection con,Vector<Object> log) {
    this.m_con = con;
    this.m_fields = new Hashtable<String, SQLField>();
    this.m_tablename = tablename;
    this.m_primarykeys = new Vector<FieldMap>();
    this.m_log = log;
  }
  public Vector<Object> getLog(){
      return this.m_log;
  }
  public void addPrimaryKey(FieldMap field) throws Exception{
      this.m_primarykeys.add(field);
  }
  public void setField(String fieldname,Object value,String type) throws Exception{
	  //String str = ""+value;
	  //System.out.println("Field:"+fieldname+"("+str.length()+")="+value);
      int sqltype = java.sql.Types.VARCHAR;
      if(type.toLowerCase().equals("text")){
          sqltype = java.sql.Types.VARCHAR;
          if(value instanceof byte[]){
        	  value = new String((byte[])value);
          }
      }else if(type.toLowerCase().equals("integer")){
          sqltype = java.sql.Types.INTEGER;
          if(value != null && value instanceof String){
        	  String retval = "";
        	  for(int i = 0; i < value.toString().length();i++){
        		  char c = value.toString().charAt(i);
        		  if(c == '0' || c == '1'||c == '2'||c =='3'||c=='4'||c=='5'||c=='6'||c=='7'||c=='8'||c=='9'){
        			  retval += c;
        		  }
        		  
        	  }
        	  if(retval.length() == 0){
    			  retval = null;
    		  }
        	  value = retval;
          }
      }else if(type.toLowerCase().equals("double")){
          sqltype = java.sql.Types.DOUBLE;
          if(value != null){
              Double d = (Double)value;
              if(d.isNaN()){
                  value = null;
              }else if(d.isInfinite()){
                  value = null;
              }
          }
      }else if(type.toLowerCase().equals("date")){
          sqltype = java.sql.Types.DATE;
          if(value instanceof java.util.Date){
        	  Calendar cal = Calendar.getInstance();
        	  cal.setTime((java.util.Date)value);
        	  int year = cal.get(Calendar.YEAR);
        	  if(year < 1800){
        		  return;
        	  }
          }
      }else if(type.toLowerCase().equals("boolean")){
          sqltype = java.sql.Types.BIT;
          if(value != null){
              if(value instanceof String){
                  if(value.toString().toLowerCase().trim().equals("no")){
                      value = new Integer(0);
                  }else if(value.toString().trim().length() == 0){
                	  value = new Integer(0);
                  }else{
                      value = new Integer(1);
                  }
              }else if(value instanceof Integer){
                  if(!value.toString().equals("0")){
                      value = new Integer(1);
                  }
              }else if(value instanceof Double){
                  if(((Double)value).doubleValue() != 0){
                      value = new Integer(1);
                  }else{
                      value = new Integer(0);
                  }
              }else{
                  value = new Integer(1);
              }
          }else{
              value = new Integer(0);
          }
      }else if(type.toLowerCase().equals("binary")){
    	  sqltype = java.sql.Types.VARBINARY;
      }
      if(value != null && value instanceof java.util.Date && type.toLowerCase().equals("datetime")){
          java.sql.Timestamp d = new java.sql.Timestamp(((java.util.Date)value).getTime());
          value = d;
      }else if(value != null && value instanceof java.util.Date && type.toLowerCase().equals("date")){
    	  DateType date_only = new DateType((java.util.Date)value);
    	  java.sql.Date d = (java.sql.Date)date_only.getValue();
    	  value = d;
      }else if(value != null && value instanceof EmbeddedObject){
    	  EmbeddedObject o = (EmbeddedObject)value;
    	  try{
    		  byte[] buffer = new byte[o.getFileSize()];
    		  o.getInputStream().read(buffer);
    		  if(sqltype == java.sql.Types.VARCHAR){
    			  value = new String(buffer);
    		  }else if(sqltype == java.sql.Types.VARBINARY){
    			  value = buffer;
    		  }
    	  }catch(Exception e){
    		  e.printStackTrace();
    		  throw e;
    	  }
      }
      SQLField field = new SQLField(value,sqltype);
      this.m_fields.put(fieldname,field);
  }
  @SuppressWarnings("unchecked")
protected String getPreparedUpdateSQL() throws Exception {
    String retval = "update " + this.m_tablename + " set ";
    Enumeration keys = this.m_fields.keys();
    this.m_keys = new Vector();
    int i = 0;
    while(keys.hasMoreElements()){
      String field = keys.nextElement().toString();
      this.m_keys.add(field);
      if(i > 0) retval += ",";
      retval += field + " = ? ";
      i++;
    }
    retval += " where ";
    for(i = 0; i < this.m_primarykeys.size();i++){
        FieldMap field = (FieldMap)this.m_primarykeys.get(i);
        if (i > 0) {
            retval += " and ";
        }
        retval += field.getString(FieldMap.SQLFIELDNAME) + " = ?";
    }
    return retval;
  }
  protected String getPreparedInsertSQL(){
    this.m_keys = new Vector<String>();
    String retval = "insert into " + this.m_tablename;
    String fieldnames = "(";
    String values = "(";
    Enumeration<String> keys = this.m_fields.keys();
    int i = 0;
    while(keys.hasMoreElements()){
      String fieldname = keys.nextElement().toString();
      SQLField field = (SQLField)this.m_fields.get(fieldname);
      if(field.value != null){
          if (i > 0) {
              fieldnames += ",";
              values += ",";
          }
          this.m_keys.add(fieldname);
          fieldnames += fieldname;
          values += "?";
          i++;
      }
    }
    fieldnames += ")";
    values += ")";
    retval += fieldnames + " values " + values;
    return retval;
  }
  protected String getPreparedSearchSQL() throws Exception {
    String keys = "";
    FieldMap field = (FieldMap)this.m_primarykeys.get(0);
    keys += field.getString(FieldMap.SQLFIELDNAME);
    String retval = "select "+keys+" from " + this.m_tablename;
    retval += " where ";
    for(int i = 0; i < this.m_primarykeys.size();i++){
        if (i > 0) {
            retval += " and ";
        }
        field = (FieldMap)this.m_primarykeys.get(i);
        retval += field.getString(FieldMap.SQLFIELDNAME) + " = ?";
    }
    return retval;
  }
  public boolean hasPrimaryKeys() throws Exception{
      boolean retval = true;
      for(int i = 0; i < this.m_primarykeys.size();i++){
          FieldMap field = (FieldMap)this.m_primarykeys.get(i);
          String key = field.getString(FieldMap.SQLFIELDNAME);
          if(this.m_fields.get(key) == null){
              retval = false;
          }else{
              try{
                  SQLField sqlfield = (SQLField)this.m_fields.get(key);
                  if (sqlfield.value == null) {
                      retval = false;
                  }
              }catch(Exception e){
                  retval = false;
              }
          }
      }
      return retval;
  }
  public void remove_keys(Vector<FieldMap> vector) throws Exception {
      if(vector.size() > 0){
          String sql;
          PreparedStatement ps;
          sql = "delete from " + this.m_tablename + " where ";
          for(int i = 0; i < vector.size();i++){
              FieldMap fm = (FieldMap)vector.get(i);
              if(i > 0){
                  sql += " and ";
              }
              sql += fm.getString(FieldMap.SQLFIELDNAME);
              sql += " = ?";
          }
          ps = this.m_con.prepareStatement(sql);
          for(int i = 1; i <= vector.size();i++){
              FieldMap fm = (FieldMap)vector.get(i-1);
              SQLField f = (SQLField)this.m_fields.get(fm.getString(FieldMap.SQLFIELDNAME));
              ps.setObject(i,f.value);
          }
          ps.executeUpdate();
      }
  }
  public void save() throws Exception {
      if ((this.hasPrimaryKeys() == false && this.m_primarykeys.size() > 0)
          || this.m_fields.size() == 0) {
          return;
      }
      String sql;
      PreparedStatement ps;
      boolean has_record = false;
      if(this.m_primarykeys.size() > 0){
          sql = this.getPreparedSearchSQL();
          //System.out.println(sql);
          //this.m_log.add(sql);
          ps = this.m_con.prepareStatement(sql);
          String keys = "";
          for(int i = 0; i < this.m_primarykeys.size();i++){
              FieldMap field = (FieldMap)this.m_primarykeys.get(i);
              if(i > 0) keys += ",";
              SQLField f = (SQLField)this.m_fields.get(field.getString(FieldMap.SQLFIELDNAME));
              keys += f.value.toString();
              ps.setObject(i+1, f.value);
          }
          //System.out.println("Searching for record: " + keys);
          //this.m_log.add("Searching for record: " + keys);
          ResultSet rs = ps.executeQuery();
          while (rs.next()) {
              has_record = true;
              sql = this.getPreparedUpdateSQL();
              //System.out.println(sql);
              //this.m_log.add(sql);
              ps = this.m_con.prepareStatement(sql);
              this.populateUpdatePreparedStatement(ps);
              ps.executeUpdate();
              ps.close();
          }
          rs.close();
      }
      if(has_record == false){
          sql = this.getPreparedInsertSQL();
          //System.out.println(sql);
          //this.m_log.add(sql);
          ps = this.m_con.prepareStatement(sql);
          this.populateInsertPreparedStatement(ps);
          ps.executeUpdate();
          ps.close();
      }
  }

  /**
   * populateInsertPreparedStatement
   *
   * @param ps PreparedStatement
   */
  protected void populateInsertPreparedStatement(PreparedStatement ps) throws
      SQLException {
    if(this.m_keys == null){
        return;
    }
    for(int i = 1;i <= this.m_keys.size();i++){
        SQLField f = (SQLField)this.m_fields.get(this.m_keys.get(i-1));
        if(f.value == null){
            ps.setNull(i,f.sqltype);
        }else{
        	if(f.value instanceof lotus.domino.local.EmbeddedObject){
        		try {
					InputStream in = ((lotus.domino.local.EmbeddedObject)f.value).getInputStream();
					ByteArrayOutputStream bout = new ByteArrayOutputStream();
					byte[] buffer = new byte[1024];
					for(int bytes = in.read(buffer);bytes > -1;bytes = in.read(buffer)){
						bout.write(buffer,0,bytes);
					}
					f.value = bout.toByteArray();
				} catch (NotesException e) {
					throw new SQLException(e);
				} catch (IOException e) {
					throw new SQLException(e);
				}
        	}
            ps.setObject(i, f.value);
        }
    }
  }

  /**
   * populateUpdatePreparedStatement
   *
   * @param ps PreparedStatement
   */
  protected void populateUpdatePreparedStatement(PreparedStatement ps) throws
      Exception {
    if(this.m_keys == null){
        return;
    }
    int i = 1;
    for(;i <= this.m_keys.size();i++){
        SQLField f = (SQLField)this.m_fields.get(this.m_keys.get(i-1));
        if(f.value == null){
            ps.setNull(i,f.sqltype);
        }else if(f.value instanceof java.sql.Timestamp){
        	java.sql.Timestamp d = (java.sql.Timestamp)f.value;
        	ps.setTimestamp(i, d);
        }else if(f.value instanceof java.util.Date){
        	java.sql.Date d = new java.sql.Date(((java.util.Date)f.value).getTime());
        	ps.setDate(i, d);
    	}else{
            ps.setObject(i, f.value);
        }
    }
    for(int j = 0;j < this.m_primarykeys.size();j++){
        FieldMap field = (FieldMap)this.m_primarykeys.get(j);
        SQLField f = (SQLField)this.m_fields.get(field.getString(FieldMap.SQLFIELDNAME));
        ps.setObject(i + j,f.value);
    }
  }
  protected class SQLField{
      public Object value;
      public int sqltype;
      SQLField(Object val,int type){
    	  this.setValue(val);
          this.sqltype = type;
      }
	private void setValue(Object val) {
		try{
			if(val instanceof EmbeddedObject){
				EmbeddedObject o = (EmbeddedObject)val;
				InputStream in = o.getInputStream();
				byte[] buffer = new byte[1024];
				ByteArrayOutputStream bout = new ByteArrayOutputStream();
				for(int i = in.read(buffer);i > -1;i = in.read(buffer)){
					bout.write(buffer,0,i);
				}
				this.value = bout.toByteArray();
			}else{
				this.value = val;
			}
		}catch(Exception e){
			e.printStackTrace();
		}
	}
  }
}
